package uk.ac.shef.ecomm.jdbc.mysql;

import java.sql.PreparedStatement;
import java.sql.SQLException;

import uk.ac.shef.ecomm.*;


public class ManageTimeSlots extends mysqlDataAccess
{

	private PreparedStatement sqlInsertTimeSlot;
	private PreparedStatement sqlUpdateReserveTimeSlot;
	private PreparedStatement sqlUpdateTimeSlotStatus;
	
	public ManageTimeSlots()
	{
		try
		{
			connect();
			sqlInsertTimeSlot = connection.prepareStatement("INSERT into " + TimeSlotsTBL + " (proj_id,date,hour,status) VALUES (?,?,?,?)");
			sqlUpdateReserveTimeSlot = connection.prepareStatement("UPDATE " + TimeSlotsTBL + " SET part_email=? WHERE proj_id=? and date=? and time=? and status=?");
			sqlUpdateTimeSlotStatus = connection.prepareStatement("UPDATE " + TimeSlotsTBL + " SET status=? WHERE proj_id=? and part_email=? and date=? and time=?");
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}
	}
	
	public boolean InsertTimeSlot(TimeSlot ts)
	{
		try
		{
			int result;
			
			sqlInsertTimeSlot.setInt(1, ts.getPr().getId());
			sqlInsertTimeSlot.setDate(2, ts.getDate());
			sqlInsertTimeSlot.setInt(3, ts.getHour());
			sqlInsertTimeSlot.setString(4, ts.FREE);
			
			result = sqlInsertTimeSlot.executeUpdate();
			if (result == 0)
			{
				connection.rollback();
				return false;
			}
			
			connection.commit();
			return true;
			
		}
		catch (com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException sqle)
		{
			System.out.println(sqle.toString());
			return false;
			
		}
		catch (SQLException sqle)
		{
			sqle.printStackTrace();
			return false;
		}
	}
	public boolean ReserveTimeSlot(TimeSlot ts, Participant pt)
	{
		try
		{
			int result;
			
			sqlUpdateReserveTimeSlot.setString(1, pt.getEmail());
			sqlUpdateReserveTimeSlot.setInt(2, ts.getPr().getId());
			sqlUpdateReserveTimeSlot.setDate(3, ts.getDate());
			sqlUpdateReserveTimeSlot.setInt(4, ts.getHour());
			sqlUpdateReserveTimeSlot.setString(5, ts.FREE);
			
			result = sqlUpdateReserveTimeSlot.executeUpdate();
			if (result == 0)
			{
				connection.rollback();
				return false;
			}
			
			connection.commit();
			return true;
			
		}
		catch (com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException sqle)
		{
			System.out.println(sqle.toString());
			return false;
			
		}
		catch (SQLException sqle)
		{
			sqle.printStackTrace();
			return false;
		}
	}
	//Finding Free Time slots --TBD
	public boolean UpdateTimeSlotStatus(TimeSlot ts, String status)
	{
		try
		{
			int result;
			
			sqlUpdateTimeSlotStatus.setString(1, status);
			sqlUpdateTimeSlotStatus.setInt(2, ts.getPr().getId());
			sqlUpdateTimeSlotStatus.setString(3, ts.getPt().getEmail());
			sqlUpdateTimeSlotStatus.setDate(4, ts.getDate());
			sqlUpdateTimeSlotStatus.setInt(5, ts.getHour());
			
			result = sqlUpdateTimeSlotStatus.executeUpdate();
			if (result == 0)
			{
				connection.rollback();
				return false;
			}
			
			connection.commit();
			return true;
			
		}
		catch (com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException sqle)
		{
			System.out.println(sqle.toString());
			return false;
			
		}
		catch (SQLException sqle)
		{
			sqle.printStackTrace();
			return false;
		}
	}
	public void close()
	{
		try
		{
			sqlInsertTimeSlot.close();
			sqlUpdateReserveTimeSlot.close();
			sqlUpdateTimeSlotStatus.close();
			connection.close();
		}
		catch (SQLException sqle)
		{
			sqle.printStackTrace();
		}
	}
}
